﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBAccess;
using SSPharma.Common;
using SSPharma.Model;
using System.Data.Common;
using SSPharma.Biz.Common;
using System.Transactions;

namespace SSPharma.Biz.DanhMuc
{
    class BizDanhMucKhoanMuc
    {
        //khai báo hàm Common dùng để lấy các hàm dùng chung
        private BizCommon _BizCommon = new BizCommon();
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// lấy khoản mục theo mã khoản mục
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataDanhMucKhoanMucTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.* from HT_DM_KhoanMuc a");
            strSQL.Append(" where SoKhoanMuc = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);
            return Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lay cau truc de luu du lieu xong database
        /// </summary>
        /// <returns></returns>
        public DataSet fs_LayCauTrucTable()
        {
            DataSet dsdata = new DataSet();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.* from HT_DM_KhoanMuc a");
            strSQL.Append(" where 1=2");
            DataTable dt_KhoanMuc = Global.DataFactory.GetDataTable(strSQL.ToString());
            dt_KhoanMuc.TableName = "dt_KhoanMuc";
            dsdata.Tables.Add(dt_KhoanMuc);
            strSQL.Clear();
            strSQL.Append(" SELECT Choose = convert(bit,0), Module,TextModule from HT_DM_Module");
            DataTable dt_Module = Global.DataFactory.GetDataTable(strSQL.ToString(),null);
            dt_Module.TableName = "dt_Module";
            dsdata.Tables.Add(dt_Module);
            return dsdata;
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Them danh muc khoản mục.
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TienTo"></param>
        public void fg_InsertDanhMucKhoanMuc(DataTable dt, string TienTo)
        {
            try
            {
                string query_KhoanMuc = "Select * from HT_DM_KhoanMuc where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_KhoanMuc);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Cap nhat danh muc khoản mục
        /// </summary>
        /// <param name="dt"></param>
        public void fg_UpdateDanhMucKhoanMuc(DataTable dt)
        {
            try
            {
                string query_KhoanMuc = "Select * from HT_DM_KhoanMuc where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_KhoanMuc);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        
        /// <summary>
        /// User: TAMNN 23/07/2014
        /// Mục đích: lấy danh sách theo danh mục
        /// </summary>
        /// <returns></returns>
        public DataTable ft_GetListdata(IList<string> list, string Orderby)
        {
            string str = string.Join(",", list.ToArray());
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT ");
            strSQL.Append(str);
            strSQL.Append(" from HT_DM_KhoanMuc with(nolock)");
            strSQL.Append(!string.IsNullOrEmpty(Orderby) ? (" order by " + Orderby) : "");
            return Global.DataFactory.GetDataTable(strSQL.ToString());
        }

        /// <summary>
        /// User:TAMNN 28/07/2014 
        /// Mục đích: kiểm tra mã loại ct đã tồn tại hay chưa
        /// </summary>
        /// <param name="p_sTableName"></param>
        /// <param name="p_sfill"></param>
        /// <param name="p_sGiatri"></param>
        /// <param name="p_sDieuKien"></param>
        /// <returns></returns>
        public string fs_CheckDataExistsKhoanMuc(string p_sGiatri)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_KhoanMuc", "KhoanMuc", "KhoanMuc  = N'" + p_sGiatri +"'");
        }

        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox khoản mục
        /// </summary>
        /// <returns></returns>
        public IList<LoaiTien> fl_DSDanhMuc_LoaiTien()
        {
            List<LoaiTien> items = new List<LoaiTien>();
            LoaiTien item;
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT MaLoaiTien,TenLoaiTien");
            strSQL.Append(" FROM HT_DM_LoaiTien");
            strSQL.Append(" ORDER BY ID_SQ");
            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString()))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        item = new LoaiTien();
                        item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                        item.Name = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
                        items.Add(item);
                    }
                }
            }

            return items;
        }
    }
}
